<?php

class Core_Model_DbTable_Votes extends YouNet_Model_DbTable_Users
{
	const TABLE_NAME = 'entry_votes';
	const STATISTIC_KEY = 'vote';
	
	const TOTAL_VOTER_NAME = "Total Voters: ";
	const TOTAL_VOTER_KEY = "total_voter";
	
	protected $_rowClass = 'Core_Model_Vote';
	protected $_name = self::TABLE_NAME;

	/**
	 *
	 * @param type $id_entry
	 * @param type $id_user
	 * @return type 
	 */
	public function findTheLastestVoteOfUser($id_entry, $id_user)
	{
		$query = $this->select()
				->from($this, array('id', 'time_voted', 'count(id) as is_voted'))
				->where('id_entry=?', $id_entry)
				->where('id_user=?', $id_user)
				->order('id desc')
				->limit(1);

		$data = $this->fetchAll($query)->current();
		
		return $data;
	}
	
	public function isVote($id_entry,$id_user)
	{
		$key		= Core_Cache_IKeys::MEMCACHE_USER_ENTRY_IS_VOTED.$id_user.'_'.$id_entry;
		$value		= YouNet_Util::getMemCache()->load($key);
		if( $value === false)
		{
			$voteRow	= $this->findTheLastestVoteOfUser($id_entry,$id_user);
			$value		= $voteRow->is_voted;
			YouNet_Util::getMemCache()->save((int)$value,$key);
		}
		return ($value > 0);
	}
	
	public function countUserVotesByContest($id_contest, $id_user)
	{
		$query = $this->select()
				->setIntegrityCheck(false)
				->from(array('ev'=>self::TABLE_NAME), array('ev.id', 'ev.time_voted', 'count(ev.id) as num_of_votes'))
				->joinLeft(array('ce' => 'contest_entries'), 'ce.id=id_entry', array())
				->where('ce.id_contest=?', $id_contest)
				->where('ev.id_user=?', $id_user);
		$result = $this->fetchAll($query)->current();
		
		return $result->num_of_votes;
	}
	
	public function getAllVotersByContest($id_contest)
	{
		$select = $this->select()->setIntegrityCheck(false);
		$select->from(
			array('v' => self::TABLE_NAME),
			array(
				'votes'			=> new Zend_Db_Expr('count(v.id)'),
				'time_voted'	=> 'time_voted'
			)
		)->join(
			array('e' => Core_Model_DbTable_Entries::TABLE_NAME ),
			'v.id_entry = e.id AND e.id_contest = '.$id_contest,
			array()
		)->join(
			array('u' => Core_Model_DbTable_Users::TABLE_NAME),
			'u.id = v.id_user',
			array(
				'name',
				'link',
				'birthday',
				'gender',
				'email'
			)
		)->group(
			'u.id'
		);
		return $this->fetchAll($select);
	}
	
	/**
	 * Adding additional day to get the right data as we are not stored date as date type but as INT
	 * @param type $id_contest
	 * @param type $params
	 * @return type
	 */
	public static function getVoteStatistic($id_contest, $params)
	{
		$instance = new self();
		$key = Core_Cache_IKeys::STATISTIC_REPORT_VOTE_TRAFFIC . $id_contest . "_" . $params['fromDate'] . "_" . $params['toDate'];
		$statistic = YouNet_Util::getObjectMemCache()->load($key);
		if ($statistic === false)
		{
			$select = $instance->select()
					->setIntegrityCheck(false)
					->from(array('p' => self::TABLE_NAME), array(
						'number'	=> new Zend_Db_Expr('count(DISTINCT(p.id_user))'),
						'date'		=> new Zend_Db_Expr('DATE(FROM_UNIXTIME(p.time_voted))')		
					 ))
					->join(array('e' => Core_Model_DbTable_Entries::TABLE_NAME), 'e.id = p.id_entry', array())
					->where('p.time_voted >= ?', $params['fromDate'])
					->where('p.time_voted <= ?', $params['toDate'] + 24*60*60)
					->where('e.id_contest = ?', $id_contest)
					->group('date');
			$data = $instance->fetchAll($select)->toArray();
			
			$yDateViews = array();
			$default = YouNet_Db_TableReport::getDefaultDateRangeArray($params['fromDate'], $params['toDate']);
			foreach ($data as $v)
				$yDateViews[$v['date']] = intval($v['number'], 10) ;
			$yDateViews = array_merge($default, $yDateViews); 

			$statistic = array(
				'younet_id_key'	=> self::STATISTIC_KEY,
				'name'	=> self::TOTAL_VOTER_NAME,
				'color' => '#EA911F',
				'data'	=> array_values($yDateViews)
			);
		
			YouNet_Util::getObjectMemCache()->save($statistic, $key, array(), REPORT_CACHE_TIME);
		}
		return $statistic;
	}
	
	public static function getVoteDetailStatistic($id_contest, $params)
	{
		$key = Core_Cache_IKeys::STATISTIC_REPORT_VOTE_DEMOGRAPHIC . $id_contest . "_" . $params['fromDate'] . "_" . $params['toDate'];
		$data = YouNet_Util::getObjectMemCache()->load($key);
		if ($data === false)
		{
			$instance = new self();
			$ageRange  = YouNet_Db_TableReport::getAgeRange();
			$todayYear = Date("Y",time());
			$defaultArr = array();
			$final = array();
			$caseStatement = "CASE ";
			foreach($ageRange as $r)
			{
				$then = '';
				$when = "WHEN u.min_age >= ". $r['min'] . " ";
				$then .= $r['min'];
				if (isset($r['max']))
				{
					$when .= "AND u.min_age <= " . $r['max'] . " ";
					$then .= "-" . $r['max'];
				}
				else
					$then .= "+";
				$when1 = $when;
				$when .= "AND u.gender = 'female' THEN '" . $then . "-female' ";
				$when1 .= "AND u.gender = 'male' THEN '" . $then . "-male' ";
				$defaultArr[$then . "-female"] = 0;
				$defaultArr[$then . "-male"] = 0;
				$caseStatement .= $when . $when1;
			}
            $caseStatement .= " WHEN u.gender = 'female' THEN 'Un-known-female'";
            $caseStatement .= " ELSE 'Un-known-male'";
			$caseStatement .= " END";
			$select = $instance->select()
					->setIntegrityCheck(false)
					->from(array( 'v' => self::TABLE_NAME), array(
						'grp' => new Zend_Db_Expr($caseStatement),
						'count' => new Zend_Db_Expr('COUNT(DISTINCT(u.id))')
					 ))
					->join(array( 'u' => Core_Model_DbTable_Users::TABLE_NAME), 'v.id_user = u.id', array())
					->join(array( 'c' => Core_Model_DbTable_Entries::TABLE_NAME), 'c.id = v.id_entry', array())
					->where('v.time_voted <= ?', $params['toDate'] + 24*60*60)
					->where('v.time_voted >= ?', $params['fromDate'])
					->where('c.id_contest = ?', $id_contest)
					->group('grp');
			$result = $instance->fetchAll($select)->toArray();
			$ageGenderData = array();
			foreach ($result as $r)
				$ageGenderData[$r['grp']] = $r['count'];
			$ageGenderData = array_merge($defaultArr, $ageGenderData);
			
			$range = $maleData = $femaleData = array();
			foreach ($ageGenderData as $id => $val)
			{
                $elements = explode('-', $id);
                $ele = isset($elements[2]) ? ($elements[0] . "-" .$elements[1]) : $elements[0];
                if (!in_array($ele, $range))
                    $range[] = $ele;
                if ((isset($elements[2]) && $elements[2] === 'male') || $elements[1] === 'male')
                    $maleData[] = intval($val,10);
                else
                    $femaleData[] = intval($val,10);
			}
			$data = array($range, array(
				array(
					'name'	=>	'Male',
					'data'	=>	$maleData
				),
				array(
					'name'	=>	'Female',
					'data'	=>	$femaleData
				)));
			YouNet_Util::getObjectMemCache()->save($data, $key, array(), REPORT_CACHE_TIME);
		}
		return $data;
	}
}